<?php
/**
 * Generates and executes a content query
 *
 * @author Jason Raede <jason@torchdm.com>
 * @package Concerto
 * @subpackage Initializers
 */
 
namespace application\initializers;
use application\utilities\Utility;

class ContentQuery extends ConcertoQuery {
	
	
	
	/**
	 * Initializes the main query; takes parameters from the URL
	 */
	public function initMainQuery() {
		
		//reportDebug('Main content query initiated');
		global $concerto;
		$queryVars = $concerto->queryVars;
		$query = self::buildQuery($queryVars);
		$results = DB()->getResults($query, 'object', 'application\\models\\Content');
		$this->queries[] = array('results'=>$results, 'current'=>'', 'lastPage'=>$this->lastPage, 'limit'=>$this->limit, 'currentPage'=>$this->currentPage, 'totalResults'=>$this->totalResults);
		$this->queryPointer++;
		
	}
	
	/** 
	 * Initializes a query with the given parameters
	 *
	 * @param string $params A query string of parameters
	 * @see buildQuery();
	 */
	public function initQuery($params) {
		
		$this->params = $params;
		$params = self::parseParameters($params);
		
		$query = self::buildQuery($params);
		$results = DB()->getResults($query, 'object', 'application\\models\\Content');
		$this->queries[] = array('results'=>$results, 'current'=>'', 'lastPage'=>$this->lastPage, 'limit'=>$this->limit, 'currentPage'=>$this->currentPage, 'totalResults'=>$this->totalResults);
		$this->queryPointer++;
	}
	
	/**public function getCurrentResultPointer($queriesBack = 0) {
		return current($this->queries[$this->queryPointer - $queriesBack]['results']);
		
	}
	
	public function getCurrentResult($queriesBack = 0) {
		return $this->queries[$this->queryPointer - $queriesBack]['current'];
	}
	
	public function initResult() {
		$this->queries[$this->queryPointer]['current'] = $this->getCurrentResultPointer();
		next($this->queries[$this->queryPointer]['results']);
		
	}*/
	
	/**
	 * Generates the opening SELECT statement of the SQL query based on the parameters given
	 *
	 * @param array $params The parameters provided when initializing the query
	 * @return string The generated statement
	 */
	public static function selectStatement($params) {
		global $concerto;
		$variable = '*';
		if($params['aggregate']) {
			$variable = '*, '.$params['aggregate'].' AS `aggregate`';
		}
		if($params['searchMode'] == 'fulltext') {
			$search = $params['search'];
			if($concerto->getOption('fulltext_include_description')) {
				$variable = $variable.", MATCH (`content_title`, `content_description`) AGAINST ('*$search*' IN BOOLEAN MODE) AS `fulltext`";
			}
			else {
				$variable = $variable.", MATCH (`content_title`) AGAINST ('*$search*' IN BOOLEAN MODE) AS `fulltext`";
			}
		}
		if($type = $params['type']) {
			$sql = "SELECT $variable FROM `cn_content` LEFT JOIN `cn_content_type$type` ON `cn_content`.`content_id`=`cn_content_type$type`.`c".$type."_id`";
		}
		else {
			$sql = "SELECT $variable FROM `cn_content`";
		}
		
		return $concerto->Modulator->runFilter('content_query_select_statement', $sql, $params);
	}
	
	/**
	 * Builds the query based on the given parameters.
	 *
	 * @param array $params The query parameters
	 * @return string The generated SQL query
	 */
	public function buildQuery($params) {
		global $concerto;
		
		$queries = array();
		// Content Type
		if($type = $params['type']) {
			if(is_array($type)) $type = "'".implode("', '", $type)."'";
			else $type = "'".$type."'";
			$queries['type'] = "`content_type` IN ($type)";
		}
		
		// Content ID
		if($id = $params['id']) {
			if(is_array($id)) $id = "'".implode("', '", $id)."'";
			else $id = "'".$id."'";
			$queries['id'] = "`content_id` IN ($id)";
		}
		
		// NOT Content ID
		if($notId = $params['not_id']) {
			if(is_array($notId)) $notId = "'".implode("', '", $notId)."'";
			else $notId = "'".$notId."'";
			$queries['notId'] = "`content_id` NOT IN ($notId)";
		}
		
		// Status
		if($params['status'] == 'ignore') {
		 // Maybe add something in the future
		}
		elseif($status = $params['status']) {
			$queries['status'] = "`content_status`='$status'";
		}
		else {
			$queries['status'] = "`content_status`='approved'";
		}
		
		// Author
		
		if($author = $params['author']) {
			if(is_array($author)) $author = "'".implode("', '", $author)."'";
			else $author = "'".$author."'";
			$queries['author'] = "`content_author` IN ($author)";
		}
		
		// Tags
		if($tags = $params['tags']) {
			$tagArrays = array();
			if(!is_array($tags)) $tags = array($tags);
			foreach($tags as $tag) {
				$tagArrays[] = "`content_id` IN ('".implode("', '", \application\models\Content::getContentWithTag($tag))."')";
			}
			
			$operator = ($params['tag_operator']) ? $params['tag_operator'] : 'AND';
			$queries['tags'] = '('.implode(' '.$operator.' ', $tagArrays).')';
		}
		
		// First Letter (for alphabetical display)
		if($firstLetter = $params['first_letter']) {
			if(ctype_alpha($firstLetter)) {
				$queries['first_letter'] = "`content_title` LIKE '{$firstLetter}%'";
			}
			else { // Numbers & symbols
				$queries['first_letter'] = "LEFT(`content_title`, 1) NOT REGEXP '[A-Za-z]'";
			}
		}
		
		// Child Of
		if($childOf = $params['child_of']) {
			if(is_array($childOf)) $childOf = "'".implode("', '", $childOf)."'";
			else $childOf = "'".$childOf."'";
			$queries['child_of'] = "`content_parent` IN ($childOf)";
		}
		
		// Grandchild of
		if($grandchildOf = $params['grandchild_of']) {
			if(!is_array($grandchildOf)) $grandchildOf = array($grandchildOf);

			$children = array();
			foreach($grandchildOf as $grandparent) {
				$children = array_merge($children, \application\utilities\Content::getDescendants($grandparent, 1));
			}
			$children = "'".implode("', '", $children)."'";
			$queries['grandchild_of'] = "`content_parent` IN ($children)";
		}
		
		// Great-Grandchild of
		if($greatGrandchildOf = $params['great_grandchild_of']) {
			if(!is_array($greatGrandchildOf)) $greatGrandchildOf = array($greatGrandchildOf);

			$children = array();
			foreach($greatGrandchildOf as $greatGrandparent) {
				$children = array_merge($children, \application\utilities\Content::getDescendants($greatGrandparent, 2));
			}
			$children = "'".implode("', '", $children)."'";
			$queries['great_grandchild_of'] = "`content_parent` IN ($children)";
		}
		
		
		// Admin
		if($admin = $params['admin']) 
			$queries['admin'] = "`content_id` IN ('".implode("', '", \application\utilities\User::getAdminListings($admin))."')";
		
		// Interaction
		if(($interaction = $params['interaction']) && ($user = $params['interaction_user'])) {
			if(!is_numeric($interaction)) $interaction = DB()->getVar("SELECT `ci_id` FROM `cn_content_interactions` WHERE `ci_name` LIKE '$interaction'");
			$queries['interaction'] = "`content_id` IN ('".implode("', '", \application\utilities\User::getInteractionListings($user, $interaction))."')";
		}
		
		// Timestamp Relations
		if($greaterThan = $params['timestamp_greater_than']) {
			//$greaterThan = \application\utilities\Utility::checkDate($greaterThan);
			$queries['timestamp_greater_than'] = "`content_timestamp` > '$greaterThan'";
		}
		if($lessThan = $params['timestamp_less_than']) {
			//$lessThan = \application\utilities\Utility::checkDate($lessThan);
			$queries['timestamp_less_than'] = "`content_timestamp` < '$lessThan'";
		}
		
		// Search
		if($search = $params['search']) {
			$types = DB()->getResults("SELECT `ct_id` FROM `cn_content_types` WHERE `ct_search_appear`='1'");
			$validTypes = array();
			foreach($types as $type) {
				$validTypes[] = $type->ct_id;
			}
			$validTypes = "'".implode("', '", $validTypes)."'";
			switch($params['search_mode']) {
				case 'fulltext':
					if($concerto->getOption('fulltext_include_description')) {
						$queries['search'] = "MATCH (`content_title`, `content_description`) AGAINST ('*$search*' IN BOOLEAN MODE) AND `content_type` IN ($validTypes)";
					}
					else {
						$queries['search'] = "MATCH (`content_title`) AGAINST ('*$search*' IN BOOLEAN MODE) AND `content_type` IN ($validTypes)";
					}
					break;
				case 'normal':
				default:
					$queries['search'] = "`content_title` LIKE '%$search%'";
					break;
			
			}
		}
		
		// Monthly Archives
		if(($month = $params['month']) && ($year = $params['year'])) {
			$queries['archives'] = "YEAR(`content_timestamp`) = '$year' AND MONTH(`content_timestamp`) = '$month'";
		}
		
		// Content-content relations
		if($params['content_relation'] && $params['content_relation_with']) {
			if(!is_array($params['content_relation_with'])) {
				$relationWithExplode = array($params['content_relation_with']);
			}
			else {
				$relationWithExplode = $params['content_relation_with'];
			}
			$relationWith = Utility::sqlImplodedArray($relationWithExplode);
			if(!is_numeric($params['content_relation'])) {
				// Get the ID
				$relation = DB()->getVar("SELECT `crt_id` FROM `cn_content_relation_types` WHERE `crt_name` LIKE '".$params['content_relation']."'");
			}
			else {
				$relation = $params['content_relation'];
			}
			$possibles = DB()->getResults("SELECT * FROM `cn_content_content_relations` WHERE `ccr_relation`='$relation' AND (`ccr_content1` IN ($relationWith) OR `ccr_content2` IN ($relationWith) OR `ccr_content3` IN ($relationWith))");
			$ids = array();
			foreach($possibles as $possible) {
				if(in_array($possible->ccr_content1, $relationWithExplode)) {
					$ids[] = $possible->ccr_content2;
					$ids[] = $possible->ccr_content3;
				}
				elseif(in_array($possible->ccr_content2, $relationWithExplode)) {
					$ids[] = $possible->ccr_content1;
					$ids[] = $possible->ccr_content3;
				}
				else {
					$ids[] = $possible->ccr_content1;
					$ids[] = $possible->ccr_content2;
				}
			}
			$ids = Utility::sqlImplodedArray($ids);
			$queries['content_relation'] = "`content_id` IN ($ids)";
		}
		
		// Smart Date & Time Search
		if((($date = $params['date']) && ($time = $params['time'])) || ($datetimeStart = $params['datetime_start']) || ($datetimeEnd = $params['datetime_end'])) {
			$datetimeQueries = array();
					
			$datetimeGate = 'AND'; // Make this user-controlled eventually
			
			$datetimeFields = $concerto->DB->getResults("SELECT * FROM `cn_content_fields` WHERE `cf_search_datetime`='1' AND `cf_ctype`='".$params['type']."'");
			
			foreach($datetimeFields as $field) {
				$args = array('params'=>$params, 'field'=>$field);
				switch($field->cf_data_type) {
					case 'Date & Time':
						if($datetimeStart && $datetimeEnd) {
							$datetimeStart = \application\utilities\Utility::checkDate($datetimeStart);
							$datetimeEnd = \application\utilities\Utility::checkDate($datetimeEnd);
							$datetimeQueries[] = "(`$field->cf_column` >= '".date('Y-m-d H:i:s', $datetimeStart)."' AND `$field->cf_column` <= '".date('Y-m-d H:i:s', $datetimeEnd)."')";
						}
						elseif($datetimeStart) {
							$datetimeStart = \application\utilities\Utility::checkDate($datetimeStart);
							$datetimeQueries[] = "(`$field->cf_column` >= '".date('Y-m-d H:i:s', $datetimeStart)."')";
						}
						elseif($datetimeEnd) {
							$datetimeEnd = \application\utilities\Utility::checkDate($datetimeEnd);
							$datetimeQueries[] = "(`$field->cf_column` <= '".date('Y-m-d H:i:s', $datetimeEnd)."')";
						}	
						else {
							$date = \application\utilities\Utility::checkDate($date);
							$time = \application\utilities\Utility::checkDate($time);
							$datetimeQueries[] = "`$field->cf_column` = '".date('Y-m-d', $date).' '.date('H:i:s', $time)."'";
						}
						
						break;
					default:
						$filtered = $concerto->Modulator->runFilter('content_query_datetime', '', $args);
						if($filtered) $datetimeQueries[] = $filtered;
						break;
				}
			}
			if(count($datetimeQueries)) $queries[] = '('.implode(' '.strtoupper($datetimeGate).' ', $datetimeQueries).')';
			
		}
		elseif(($date = $params['date']) || ($dateStart = $params['date_start']) || ($dateend = $params['date_end'])) {
			$dateQueries = array();
			//$date_gate = cn_get_option('l'.$this->level.'_search_date_gate');
			$dateGate = 'AND';
			$dateFields = $cndb->get_results("SELECT * FROM `cn_content_fields` WHERE `cf_search_date`='1' AND `cf_ctype`='".$params['type']."'");
			
			foreach($dateFields as $field) {
				$args = array('params'=>$params, 'field'=>$field);
				switch($field->cf_data_type) {
					case 'Date':
					case 'Date & Time':
						if($dateStart && $dateEnd) {
							$dateStart = \application\utilities\Utility::checkDate($dateStart);
							$dateEnd = \application\utilities\Utility::checkDate($dateEnd);
							$dateQueries[] = "(DATE(`$field->cf_column`) >= DATE('".date('Y-m-d H:i:s', $dateStart)."') AND DATE(`$field->cf_column`) <= DATE('".date('Y-m-d H:i:s', $dateEnd)."'))";
						}
						elseif($dateStart) {
							$dateStart = \application\utilities\Utility::checkDate($dateStart);
							$dateQueries[] = "(DATE(`$field->cf_column`) >= DATE('".date('Y-m-d H:i:s', $dateStart)."'))";
						}
						elseif($dateEnd) {
							$dateEnd = \application\utilities\Utility::checkDate($dateEnd);
							$dateQueries[] = "(DATE(`$field->lf_column`) <= DATE('".date('Y-m-d H:i:s', $dateEnd)."'))";
						}
						else {
							$date = \application\utilities\Utility::checkDate($date);
							$dateQueries[] = "DATE(`$field->lf_column`) = '".date('Y-m-d', $date)."'";
						}
						break;
					default:
						$filtered = $concerto->Modulator->runFilter('content_query_date', '', $args);
						if($filtered) $dateQueries[] = $filtered;
						break;
				}
			}
			if(count($dateQueries)) $queries[] = '('.implode(' '.strtoupper($dateGate).' ', $dateQueries).')';
			
		}
		$queries = $concerto->Modulator->runFilter('content_queries', $queries, $params);
		
		$generalOperator = ($params['general_operator'] == 'OR' || $params['general_operator'] == 'AND') ? $params['general_operator'] : 'AND';
		if(count($queries))
			$sql = ' WHERE '.implode(' '.$generalOperator.' ', $queries);
		else 
			$sql = ' ';
		
		
		$sql = self::selectStatement($params) .$sql;
		
		// Order
		if(!$params['order'] && $params['type']) {
			$params['order'] = DB()->getVar("SELECT `ct_order_column` FROM `cn_content_types` WHERE `ct_id`='".$params['type']."'");
		}
		if(!$params['order_dir'] && $params['type']) {
			$params['order_dir'] = DB()->getVar("SELECT `ct_order_direction` FROM `cn_content_types` WHERE `ct_id`='".$params['type']."'");
		}
		
		switch($params['order']) {
			case 'title':
				$orderBy = 'ORDER BY `content_title`';
				break;
			case 'timestamp':
			case 'date':
				$orderBy = 'ORDER BY `content_timestamp`';
				break;
			case 'parent':
				$orderBy = 'ORDER BY `content_parent`';
			case 'random':
				$orderBy = 'ORDER BY RAND()';
				break;
			default:
				if($params['order']) {
					$orderBy = 'ORDER BY `'.$params['order'].'`';
				}
				else {
					$orderBy = 'ORDER BY `content_title`';
				}
				break;
		}
		switch(strtolower($params['order_dir'])) {
			case 'desc':
				$orderDir = 'DESC';
				break;
			case 'asc':
			default:
				$orderDir = 'ASC'; 
		}
		$sql .= ' '.$orderBy.' '.$orderDir;
		
		// Now paginate
		$this->totalResults = DB()->getCount($sql);
		
		$page = ($params['page']) ? $params['page'] : 1;
		$limit = ($params['limit']) ? $params['limit'] : 20;
		$this->limit = $limit;
		$this->lastPage = ceil($this->totalResults / $limit);
		
		
		if($page > $this->lastPage) $page = $this->lastPage;
		
		$this->currentPage = $page;
		
		if($page < 1) $page = 1;
		
		$limitClause = " LIMIT " . ($limit*($page - 1)).", ".$limit;
		return $sql.$limitClause;
		
		
	}
	
	
}
?>